﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SVC_DAL
{
    public class UsuarioDAL
    {
        Dados objD = null;

        public UsuarioDAL()
        {
        
        }

        //inserção
        public void inserir(String nome,String login, String senha, String status, int permissao, int empresa)
        {
            SqlCommand cmd = null;
            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                cmd.CommandText = "INSERT INTO Usuario" +
                    "(usu_nome, usu_login, usu_senha, usu_status, per_codigo, emp_codigo)" +
                    " VALUES (@nome,@login, @senha, @status, @permissao, @empresa)";
                cmd.Parameters.Add(new SqlParameter("@nome", SqlDbType.VarChar)).Value = nome;
                cmd.Parameters.Add(new SqlParameter("@login", SqlDbType.VarChar)).Value = login;
                cmd.Parameters.Add(new SqlParameter("@senha", SqlDbType.VarChar)).Value = senha;
                cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar)).Value = status;
                cmd.Parameters.Add(new SqlParameter("@permissao", SqlDbType.Int)).Value = permissao;
                cmd.Parameters.Add(new SqlParameter("@empresa", SqlDbType.Int)).Value = empresa;
                objD.executacomando(cmd);
                cmd = null;
                objD = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //alterar
        public void alterar(int codigo, String nome, String login, String senha, String status, 
            int permissao, int empresa)
        {
            SqlCommand cmd = null;
            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                cmd.CommandText = "UPDATE Usuario" +
                    " SET usu_nome=@nome, usu_login=@login, usu_senha = @senha, usu_status = @status," +
                    " per_codigo = @permissao, emp_codigo = @empresa" +
                    " WHERE" +
                    " usu_codigo=@codigo";
                cmd.Parameters.Add(new SqlParameter("@codigo", SqlDbType.Int)).Value = codigo;
                cmd.Parameters.Add(new SqlParameter("@nome", SqlDbType.VarChar)).Value = nome;
                cmd.Parameters.Add(new SqlParameter("@login", SqlDbType.VarChar)).Value = login;
                cmd.Parameters.Add(new SqlParameter("@senha", SqlDbType.VarChar)).Value = senha;
                cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar)).Value = status;
                cmd.Parameters.Add(new SqlParameter("@permissao", SqlDbType.Int)).Value = permissao;
                cmd.Parameters.Add(new SqlParameter("@empresa", SqlDbType.Int)).Value = empresa;
                objD.executacomando(cmd);
            }
            catch (Exception ex)
            {

                throw ex;
            }
            cmd = null;
            objD = null;
        }

        public void excluir(int usu_codigo)
        {
            SqlCommand cmd = null;
            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Usuario" +
                    " WHERE" +
                    " usu_codigo=@usu_codigo";
                cmd.Parameters.Add(new SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usu_codigo;
                objD.executacomando(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            cmd = null;
            objD = null;
        }

        //selecionar várias linhas
        public DataTable localizar(String descricao, String coluna)
        {
            DataTable tab = null;
            SqlCommand cmd = null;
            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Usuario" +
                    " WHERE " + coluna + " like @descricao";
                cmd.Parameters.Add(new SqlParameter("@descricao",
                    SqlDbType.VarChar)).Value = descricao + "%";
                tab = objD.executaconsulta(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            cmd = null;
            objD = null;
            return tab;
        }

        public DataTable localizarLeave(String descricao, String coluna)
        {
            DataTable tab = null;
            SqlCommand cmd = null;
            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Usuario" +
                    " WHERE " + coluna + " like @descricao";
                cmd.Parameters.Add(new SqlParameter("@descricao",
                    SqlDbType.VarChar)).Value = descricao;
                tab = objD.executaconsulta(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            cmd = null;
            objD = null;
            return tab;
        }

        //selecionar uma linha - um código
        public int localizar(int codigo)
        {
            DataTable tab = null;
            SqlCommand cmd = null;

            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Usuario" +
                    " WHERE usu_codigo = @codigo";
                cmd.Parameters.Add(new SqlParameter("@codigo",
                    SqlDbType.Int)).Value = codigo;
                tab = objD.executaconsulta(cmd);
                cmd = null;
                objD = null;
                if (tab.Rows.Count > 0)
                    return int.Parse(tab.Rows[0][0].ToString());
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable localizarEmTudo(String descricao)
        {
            DataTable tab = null;
            SqlCommand cmd = null;
            try
            {
                objD = new Dados();
                cmd = new SqlCommand();

                cmd.CommandText = "SELECT TOP 100 u.usu_codigo as [Código do Usuário], u.usu_nome as Nome, u.usu_login as [Login], " +
                                    " u.usu_status as [Status], e.emp_razaoSocial as [Razão Social],p.per_nome [Permissão] ,u.per_codigo [Código da Permissão],u.emp_codigo [Código da Empresa] " +
                                    " FROM Empresa e Inner Join Usuario u ON e.emp_codigo = u.emp_codigo inner join Permissao p on u.per_codigo = p.per_codigo" +
                                    " WHERE u.usu_codigo like @descricao or u.usu_nome like @descricao or u.usu_login like @descricao or u.usu_status like @descricao" +
                                    " or e.emp_razaoSocial like @descricao or p.per_nome like @descricao";
                cmd.Parameters.Add(new SqlParameter("@descricao",
                    SqlDbType.VarChar)).Value = "%" + descricao + "%";

                tab = objD.executaconsulta(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            cmd = null;
            objD = null;
            return tab;
        }

        public DataTable localizarPrimeiroUltimo(String descricao)
        {
            DataTable tab = null;
            SqlCommand cmd = null;

            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                if (descricao == "ultimo")
                {
                    cmd.CommandText = "SELECT usu_codigo = max(usu_codigo) FROM Usuario";
                    cmd.Parameters.Add(new SqlParameter("@descricao",
                         SqlDbType.VarChar)).Value = descricao;
                }
                else if (descricao == "primeiro")
                {
                    cmd.CommandText = "SELECT usu_codigo = min(usu_codigo) FROM Usuario";
                    cmd.Parameters.Add(new SqlParameter("@descricao",
                     SqlDbType.VarChar)).Value = descricao;
                }
                tab = objD.executaconsulta(cmd);

            }
            catch (Exception)
            {

                throw;
            }
            return tab;
        }

        public DataTable localizarProxAnterior(String descricao, int codigo)
        {
            DataTable tab = null;
            SqlCommand cmd = null;

            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                if (descricao == "proximo")
                    cmd.CommandText = "SELECT usu_codigo FROM Usuario WHERE usu_codigo = (SELECT MIN(usu_codigo) FROM Usuario WHERE usu_codigo > @codigo)";

                else if (descricao == "anterior")
                    cmd.CommandText = "SELECT usu_codigo FROM Usuario WHERE usu_codigo = (SELECT MAX(usu_codigo) FROM Usuario WHERE usu_codigo < @codigo)"; ;

                cmd.Parameters.Add(new SqlParameter("@codigo", SqlDbType.Int)).Value = codigo;

                tab = objD.executaconsulta(cmd);
            }
            catch (Exception)
            {

                throw;
            }
            return tab;
        }

        public DataTable localizarLogin(String usuario, String senha)
        {
            DataTable tab = null;
            SqlCommand cmd = null;

            try
            {
                objD = new Dados();
                cmd = new SqlCommand();
                
                    cmd.CommandText = "SELECT * FROM Usuario WHERE usu_login like @usuario and usu_senha like @senha";

                    cmd.Parameters.Add(new SqlParameter("@usuario", SqlDbType.VarChar)).Value = usuario;
                    cmd.Parameters.Add(new SqlParameter("@senha", SqlDbType.VarChar)).Value = senha;

                tab = objD.executaconsulta(cmd);
            }
            catch (Exception)
            {

                throw;
            }
            return tab;
        }
    }
}
